from sqlalchemy import text

from python_mysql_dome import Users
from python_mysql_dome import session
from python_mysql_dome import init_db
from python_mysql_dome import drop_db
from flask import Flask, request, jsonify
import json

app = Flask(__name__)

@app.route('/queryUser', methods=["POST"])
def queryUser():

    city = request.args.get("city")
    print("city：", city)

    extra = request.form.get("extra")
    id = request.form.get("id")
    startTime = request.form.get("st")
    endTime = request.form.get("et")
    pageNo = int(request.form.get("pageNo"))
    pageSize = int(request.form.get("pageSize"))
    print("extra：", extra)
    print("id：", id)

    # data = json.loads(request.get_data(as_text=True))
    # print("data：", data["id"], data["name"], data["extra"])

    # print("json：", request.json)

    userList = []
    # for u in session.query(Users).all():
    #     user = {
    #         "id":u.id,
    #         "name":u.name,
    #         "extra":u.extra
    #     }
    #     userList.append(user)
    #
    # return jsonify(userList)

    query = session.query(Users)
    if extra != "":
        query = query.filter(Users.extra != extra)

    if id != "":
        query = query.filter(Users.id > id)

    if startTime != "":
        query = query.filter(Users.createdAt >= startTime)

    if endTime != "":
        query = query.filter(Users.createdAt <= endTime)

    for u in query.order_by(Users.id.desc()).limit(pageSize).offset((pageNo-1)*pageSize).all():
    # for u in session.execute('select * from users where id >:id and extra <>:extra', params={"id":3, "extra":"nb"}).fetchall():
        user = {
            "id":u.id,
            "name":u.name,
            "extra":u.extra,
            "createdAt":u.createdAt,
            "updatedAt":u.updatedAt
        }
        userList.append(user)

    return jsonify(userList)


if __name__ == '__main__':

    init_db()

    app.run(port=9000)

    # drop_db()

    # 插入
    # obj = Users(name="alex0", extra='sb')
    # session.add(obj)
    # session.add_all([
    #     Users(name="alex1", extra='sb'),
    #     Users(name="alex2", extra='sb'),
    # ])
    # session.commit()

    #修改
    # session.query(Users).filter(Users.id > 2).update({"name": "099"})
    # session.query(Users).filter(Users.id > 2).update({Users.name: Users.name + "099"}, synchronize_session=False)
    # # session.query(Users).filter(Users.id > 2).update({"id": Users.id + 1}, synchronize_session="evaluate")
    # session.commit()

    #查询
    # ret = session.query(Users).all()
    # ret = session.query(Users.name, Users.extra).all()
    # ret = session.query(Users).filter_by(name='099099').all()
    # ret = session.query(Users).filter_by(name='099099').first()
    # for u in ret:
    #     print(u.id, u.name, u.extra)

    # ret = session.query(Users).filter(text("id<:value and name=:name")).params(value=224, name='fred').order_by(Users.id).all()
    #
    # ret = session.query(Users).from_statement(text("SELECT * FROM users where name=:name")).params(name='ed').all()

    #删除
    # session.query(Users).filter(Users.id > 2).delete()
    # session.commit()

